package com.gongren.oddjob.utils;


import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

public class ExcelUtils {

    /**
     * 
     * 功能描述: 
     * 获得模板中的列数
     * @param file
     * @return
     * @throws IOException
     * Author:   yjg@gongren.com
     * Date:     2017年8月7日 下午3:14:19
     * Version: 1.0.0
     */
    public static synchronized int getExcelColumns(File file) throws IOException {
        int columns = -1;
        String fileName = file.getName();
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName
                .substring(fileName.lastIndexOf(".") + 1);
        if ("xls".equals(extension)) {
            HSSFWorkbook hwb = new HSSFWorkbook(new FileInputStream(file));
            HSSFSheet hsheet = hwb.getSheetAt(0);
            columns = hsheet.getRow(0).getLastCellNum();
        } else if ("xlsx".equals(extension) || "xlsm".equals(extension)) {
            XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
            XSSFSheet xsheet = xwb.getSheetAt(0);
            columns = xsheet.getRow(0).getLastCellNum();
        } else if ("tmp".equals(extension)) {
            XSSFWorkbook xwb = new XSSFWorkbook(new FileInputStream(file));
            XSSFSheet xsheet = xwb.getSheetAt(0);
            columns = xsheet.getRow(0).getLastCellNum();
        } else {
            throw new IOException("不支持的文件类型");
        }
        return columns;
    }

    /**
     * @param @param  file
     * @param @return
     * @param @throws IOException
     * @return List<String> (excel每行拼接成List中的String)
     * @throws
     * @Title: readExcel
     * @Description: TODO(对外提供读取excel 的方法)
     */
    public static synchronized List<String[]> readExcel(File file) throws IOException {
        String fileName = file.getName();
        //List<String> list = new ArrayList<String>();
        //根据其名称获取后缀
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName
                .substring(fileName.lastIndexOf(".") + 1);
        List<String[]> result;
        if ("xls".equals(extension)) {
            result = read2003Excel(new FileInputStream(file));
        } else if ("xlsx".equals(extension) || "xlsm".equals(extension)) {
            result = read2007Excel(new FileInputStream(file));
        } else if ("tmp".equals(extension)) {
            result = read2007Excel(new FileInputStream(file));
        } else {
            throw new IOException("不支持的文件类型");
        }
        return result;
    }

    /**
     * @param @param  file
     * @param @return
     * @param @throws IOException
     * @return List<String> (excel每行拼接成List中的String)
     * @throws
     * @Title: readExcel
     * @Description: TODO(对外提供读取excel 的方法)
     */
    public static synchronized List<String[]> readExcel(MultipartFile file) throws IOException {
        String fileName = file.getOriginalFilename();
        //List<String> list = new ArrayList<String>();
        //根据其名称获取后缀
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName
                .substring(fileName.lastIndexOf(".") + 1);
        List<String[]> result;
        if ("xls".equals(extension)) {
            result = read2003Excel(file.getInputStream());
        } else if ("xlsx".equals(extension) || "xlsm".equals(extension)) {
            result = read2007Excel(file.getInputStream());
        } else if ("tmp".equals(extension)) {
            result = read2007Excel(file.getInputStream());
        } else {
            throw new IOException("不支持的文件类型");
        }
        return result;
    }


    /**
     * 读取 office 2003 excel
     *
     * @throws IOException
     * @throws FileNotFoundException
     */
    private static List<String[]> read2003Excel(InputStream file)
            throws IOException {
        List<String[]> result = new ArrayList<String[]>();
        int rowSize = 0;
        //创建新的 excel 工作博
        HSSFWorkbook hwb = new HSSFWorkbook(file);
//        System.out.println("===============    此  " + file.getName() + "  文档一共有  " + hwb.getNumberOfSheets() + "  sheet...第一个sheet有" + hwb.getSheetAt(0).getLastRowNum() + " 行... ");
        HSSFCell cell;
        //循环列
        HSSFSheet sheet = hwb.getSheetAt(0);
        //循环每行        不包括标题  导航. 如何想要把列名也显示出来， 请把下边的1  修改为0
        for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            HSSFRow row = sheet.getRow(rowIndex);//得到每一行的数据
            if (row == null) {
                String[] values = new String[rowSize];
                Arrays.fill(values, "");
                result.add(values);
                continue;
            }
            int tempRowSize = row.getLastCellNum();
            if (tempRowSize > rowSize) {
                rowSize = tempRowSize;
            }
            String[] values = new String[rowSize];
            Arrays.fill(values, "");
            boolean hasValue = false;
            for (int columnIndex = 0; columnIndex <= row.getLastCellNum(); columnIndex++) {
                String value = "";
                cell = row.getCell(columnIndex);
                if (cell == null) {
                    continue;
                }
                switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_STRING:
                        value = cell.getStringCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                            Date date = cell.getDateCellValue();
                            if (date != null) {
                                value = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(date);
                            } else {
                                value = "";
                            }
                        } else {
                            value = new DecimalFormat("0").format(cell.getNumericCellValue());
                        }
                        break;
                    case XSSFCell.CELL_TYPE_FORMULA:
                        // 导入时如果为公式生成的数据则无值
                        try {
                            Long number = (long) cell.getNumericCellValue();
                            value = number + "";
                        } catch (Exception e) {
                            value = cell.getStringCellValue();
                        }
                        break;
                    case XSSFCell.CELL_TYPE_BOOLEAN:
                        value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                        break;
                    case XSSFCell.CELL_TYPE_BLANK:
                        value = "";
                        break;
                    case XSSFCell.CELL_TYPE_ERROR:
                        value = "";
                        break;
                    default:
                        value = cell.toString();
                }
                if (columnIndex == 0 && "".equals(value.trim())) {
                    break;
                }
                values[columnIndex] = rightTrim(value);
                hasValue = true;
            }
            if (hasValue) {
                result.add(values);
            }
        }
        return result;
    }

    /**
     * 读取Office 2007 excel
     */

    private static List<String[]> read2007Excel(InputStream file) throws IOException {

        List<String[]> result = new ArrayList<String[]>();
        int rowSize = 0;
        XSSFCell cell;
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径
        XSSFWorkbook xwb = new XSSFWorkbook(file);
        XSSFSheet sheet = xwb.getSheetAt(0);
        for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            XSSFRow row = sheet.getRow(rowIndex);
            if (row == null) {
                String[] values = new String[rowSize];
                Arrays.fill(values, "");
                result.add(values);
                continue;
            }
            int tempRowSize = row.getLastCellNum();
            if (tempRowSize > rowSize) {
                rowSize = tempRowSize;
            }
            String[] values = new String[rowSize];
            Arrays.fill(values, "");
            boolean hasValue = false;
            for (short columnIndex = 0; columnIndex < row.getLastCellNum(); columnIndex++) {
                String value;
                cell = row.getCell(columnIndex);//得到的每行的每个具体的值
                if (cell == null) {
                    continue;
                }
                switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_STRING:
                        value = cell.getStringCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_NUMERIC:
//						    value = new DecimalFormat("0").format(cell.getNumericCellValue());
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
                        if ("@".equals(cell.getCellStyle().getDataFormatString()) || "General".equals(cell.getCellStyle().getDataFormatString())) {
                            value = new DecimalFormat("0").format(cell.getNumericCellValue());
                        } else {
                            value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                        }
                        break;
                    case XSSFCell.CELL_TYPE_FORMULA:
                        // 导入时如果为公式生成的数据则无值
                        try {
                            Long number = (long) cell.getNumericCellValue();
                            value = number + "";
                        } catch (Exception e) {
                            value = cell.getStringCellValue();
                        }
                        break;
                    case XSSFCell.CELL_TYPE_BLANK:
                        value = "";
                        break;
                    case XSSFCell.CELL_TYPE_ERROR:
                        value = "";
                        break;
                    case XSSFCell.CELL_TYPE_BOOLEAN:
                        value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                        break;
                    default:
                        value = "";
                        break;
                }
                values[columnIndex] = rightTrim(value);
                hasValue = true;
            }
            if (hasValue) {
                result.add(values);
            }
        }
        return result;
    }

    /**
     * 去掉字符串右边的空格
     *
     * @param value 要处理的字符串
     * @return 处理后的字符串
     */
    public static String rightTrim(String value) {
        if (value == null || "".equals(value)) {
            return "";
        }
        int length = value.length();
        for (int i = length - 1; i >= 0; i--) {
            if (value.charAt(i) != 0x20) {
                break;
            }
            length--;
        }
        return value.substring(0, length);
    }
}